USE [master]
GO

CREATE DATABASE [IEDEventDatabase];
GO

-- The following commented statements are used to create a user with access to the IEDEventDatabase.
-- Be sure to change the username and password.
-- Replace-all from NewUser to the desired username is the preferred method of changing the username.

--IF  NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'NewUser')
--CREATE LOGIN [NewUser] WITH PASSWORD=N'MyPassword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
--GO
USE [IEDEventDatabase]
GO
--CREATE USER [NewUser] FOR LOGIN [NewUser]
--GO
--CREATE ROLE [openPDCManagerRole] AUTHORIZATION [dbo]
--GO
--EXEC sp_addrolemember N'openPDCManagerRole', N'NewUser'
--GO
--EXEC sp_addrolemember N'db_datareader', N'openPDCManagerRole'
--GO
--EXEC sp_addrolemember N'db_datawriter', N'openPDCManagerRole'
--GO


----- TABLES -----

CREATE TABLE [dbo].[Device]
(
	[ID] [int] IDENTITY(1, 1) NOT NULL,
	[DeviceCode] [varchar](200) NOT NULL,
	[StationID] [varchar](200) NOT NULL,
	
	CONSTRAINT [PK_Device] PRIMARY KEY CLUSTERED
	(
		[ID] ASC
	) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Event]
(
	[ID] [int] IDENTITY(1, 1) NOT NULL,
    [DeviceID] [int] NOT NULL,
	[Time] [datetime] NOT NULL,
    [Type] [varchar](200) NOT NULL,
    [CurrentMagnitude] [float] NOT NULL,
    [FaultLocation] [float] NULL,
    [Frequency] [float] NULL,
    [EventGroup] [int] NULL,
    [Shot] [int] NULL,
    [Targets] [varchar](200) NULL,
	
	CONSTRAINT [PK_Event] PRIMARY KEY CLUSTERED
	(
		[ID] ASC
	) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


--- FOREIGN KEYS ---

ALTER TABLE [dbo].[Event] WITH CHECK ADD CONSTRAINT [FK_Event_Device] FOREIGN KEY ([DeviceID])
REFERENCES [dbo].[Device] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Event] CHECK CONSTRAINT [FK_Event_Device]
GO
